Clickx 75/Clickx 75.iso/software/expressionweb/expressionwebv3/ExpressionWeb_en.exe/Setup/WeNoLoc.cab/xweb.FPCLASS.dbquery.ascx

Text File | 2009-06-09 | 15.9 KB | 419 lines

<%BeginASP%>@ Import Namespace="System.Collections" <%EndASP%>
<%BeginASP%>@ Import Namespace="System.Collections.Specialized" <%EndASP%>
<%BeginASP%>@ Import Namespace="System.Globalization" <%EndASP%>
<%BeginASP%>@ Control ClassName="DBQuery" debug="true" Language="c#" <%EndASP%>
<SCRIPT language="C#" Runat="server">
        private System.Web.UI.Control _querySourceControl;
        private string _querySourceString;
        private bool _submitPressed = false;
        private bool _resetPressed = false;
        private bool _showDebug = false;

        public string QuerySource
        {
            get{
                return _querySourceString;
            }
            set{
                _querySourceString =value;
            }
        }

        public bool Submitted
        {
            get {
                return _submitPressed;
            }
        }

        protected override void OnInit(EventArgs e)
        {
            _querySourceControl = Page.FindControl(QuerySource);
        }

        static public string GetUserField(DBQuery query, string fieldName)
        {
            if( query != null )
            {
                System.Web.UI.Control theControl = query._querySourceControl.FindControl( fieldName );
                if( theControl != null && theControl is TextBox )
                    return query.TextBoxField( (TextBox)theControl ).Replace( "'", "''" );
            }

        if(HttpContext.Current.Request.QueryString[fieldName] != null)
                return HttpContext.Current.Request.QueryString[fieldName];

            if(HttpContext.Current.Request.Form[fieldName] != null)
                return HttpContext.Current.Request.Form[fieldName];

            return string.Empty;
        }

        public string TextBoxField( TextBox theBox )
        {
            if( _resetPressed )
            {
                theBox.Text = string.Empty;
                return string.Empty;
            }

            string Result = theBox.Text;
            if( Result.Length == 0 && theBox.Attributes["Default"] != null )
                Result = theBox.Attributes["Default"];

            return Result;
        }

        public void ClickSubmitButton(Object s, EventArgs e)
        {
            _submitPressed = true;
        }

        public void ClickResetButton(Object s, EventArgs e)
        {
            _submitPressed = true;
            _resetPressed = true;
        }


        /* Params:

        /* whereClause - the text of the query after the word "Where" that has values from the search form inserted into it
         */
        static public string CompleteWhereClause( DBQuery query, string whereClause )
        {
            // This method parses the 'WHERE' clause of a FPDB:DBRegion tag. It replaces
            // ::FieldName:: terms with information from user fields, and removes
            // 'ColumnName op ::FieldName::' phrases if the field name is not found or
            // there is no value entered and no default.
            //
            // This method does *not* do any other syntax checking. If there is an error
            // in the syntax of the clause, it will fall through to the DataView and an
            // exception will be thrown from there.

            string    Result = string.Empty;

            string    FieldName    = string.Empty,
                Token        = string.Empty,
                Phrase        = string.Empty,
                Conjunction    = string.Empty;

            int        StartIndex = 0,
                EndIndex = 0;
            bool    InField = false,
                ValidPhrase = true,
                FirstPhrase = true;

            string    DebugString        = string.Empty;

            while( EndIndex < whereClause.Length )
            {
                // Get a "term", either a string of letters and digits or some other single character
                if( !System.Char.IsLetterOrDigit( whereClause, EndIndex ) )
                    EndIndex++;
                else
                    while( System.Char.IsLetterOrDigit( whereClause, EndIndex ) && ++EndIndex < whereClause.Length );

                Token = whereClause.Substring( StartIndex, EndIndex - StartIndex );

                if( InField )
                {
                    // We're parsing a field name ...
                    if( Token == ":" && whereClause[ StartIndex + 1 ] == ':' )
                    {
                        // ... and we've reached the end ...
                        EndIndex = StartIndex += 2;
                        InField = false;
                        if(!System.Char.IsLetter(FieldName,0))
                        {
                            FieldName = "Field" + FieldName;
                        }
                        if( GetUserField( query, FieldName ).Length != 0 )
                        {
                            Phrase += GetUserField( query, FieldName ).Replace( "'", "''" );
                        }
                        else
                        {
                            // ... but it doesn't exist or the user didn't enter anything,
                            ValidPhrase = false;
                        }
                    }
                    else
                    {
                        // ... and we continue to parse the field name
                        FieldName += Token;
                        StartIndex = EndIndex;
                    }
                }
                else
                {
                    DebugString += Token + ";";
                    if( Token == ":" && whereClause[ StartIndex + 1 ] == ':' )
                    {
                        // This is the beginning of a field
                        EndIndex = StartIndex += 2;
                        InField = true;
                        FieldName = string.Empty;
                    }
                    else if( Token.Equals("(") )
                    {
                        // An open paren gets passed on to the result
                        Result += Token;
                        StartIndex = EndIndex;
                    }
                    else if( Token.Equals(")") )
                    {
                        // A close paren signals the end of a phrase

                        if( ValidPhrase )
                        {
                            if( !FirstPhrase )
                            {
                                Result += Conjunction;
                            }
                            Result += Phrase;
                            Conjunction = Token;
                            FirstPhrase = false;
                        }

                        // But it might be empty, so read backward through the result and find out
                        // if there's anything between this paren and its match
                        int nIndex = Result.Length;
                        while( System.Char.IsWhiteSpace( Result, --nIndex ) )
                            if( nIndex == 0 )
                            {
                                throw new ApplicationException( "<%IDS_DBREGION_ASPNET_ERROR_PARAMETER_COLONCOLON_DELIM%>" );
                            }

                        if( Result[ nIndex ] == '(' )
                        {
                            Result = ( nIndex > 0 ) ? "" : Result.Substring( 0, nIndex );
                        }
                        else
                        {
                            Result += ")";
                            Conjunction = string.Empty;
                            Phrase = string.Empty;
                            ValidPhrase = true;
                        }
                        StartIndex = EndIndex;
                    }
                    else if( Token.ToLower(CultureInfo.InvariantCulture ).Equals( "or" ) || Token.ToLower(CultureInfo.InvariantCulture).Equals( "and" ) )
                    {
                        // This is a conjunction between phrases
                        if( ValidPhrase )
                        {
                            if( !FirstPhrase )
                                Result += Conjunction;
                            Result += Phrase;
                            Conjunction = Token;
                            FirstPhrase = false;
                        }
                        else if( !Conjunction.ToLower(CultureInfo.InvariantCulture).Equals( "or" ) )
                            // The last phrase wasn't included by the user. We'll preserve "or" and move on.
                            Conjunction = Token;

                        Phrase = string.Empty;
                        StartIndex = EndIndex;
                        ValidPhrase = true;
                    }
                    else
                    {
                        // This is just part of a phrase
                        Phrase += Token;
                        StartIndex = EndIndex;
                    }
                }
            }

            if( InField )
            {
                throw new ApplicationException( "<%IDS_DBREGION_ASPNET_ERROR_PARAMETER_COLONCOLON_DELIM%>" );
            }
            if( ValidPhrase )
            {
                Result += Conjunction + Phrase;
            }
            if( query != null && query._showDebug)
            {
                query.debugOutputLabel.Text = Result;
            }

            return Result;
        }

        public static String CompleteCustomSql(String sqlText, DBQuery query, ArrayList parameterList)
        {
            int curChar = 0;
            int charMax = 0;
            int colonStart = 0;
            int SQuoteStart = 0;
            int DQuoteStart = 0;
            int Start = 0;
            int End = 0;

            parameterList.Clear();

            while ((((curChar + 5) < sqlText.Length) && sqlText.IndexOf("::", curChar) >= 0))
            {
                charMax = sqlText.Length;
                colonStart = sqlText.IndexOf("::", curChar);
                SQuoteStart = sqlText.IndexOf('\'', curChar);
                DQuoteStart = sqlText.IndexOf('"', curChar);

                if (SQuoteStart == -1)
                {
                    SQuoteStart = charMax;
                }

                if (DQuoteStart == -1)
                {
                    DQuoteStart = charMax;
                }


                char QuoteDelim = '\0';
                int QuoteStart = -1;
                int QuoteEnd = charMax;
                bool fp_bQuoteFound = false;
                int PotQuoteEnd = 0;

                if (colonStart > SQuoteStart && DQuoteStart > SQuoteStart)  //single quote is first sought for character
                {
                    QuoteDelim = '\'';
                    QuoteStart = SQuoteStart;
                }
                else if (colonStart > DQuoteStart && SQuoteStart > DQuoteStart)  //double quote is first sought for character
                {
                    QuoteDelim = '"';
                    QuoteStart = DQuoteStart;
                }
                else
                {
                    QuoteStart = colonStart;
                    //The :: comes before any ' or "
                }

                if(QuoteDelim != '\0')
                {
                    PotQuoteEnd = QuoteStart + 1;
                    while (fp_bQuoteFound == false && PotQuoteEnd < (charMax - 1))
                    {

                        PotQuoteEnd = sqlText.IndexOf(QuoteDelim, PotQuoteEnd);

                        if(PotQuoteEnd == -1)
                        {
                            break;
                        }

                        if(PotQuoteEnd == (charMax - 1))
                        {
                            QuoteEnd = PotQuoteEnd;
                            fp_bQuoteFound = true;
                            break;
                        }

                        if(!sqlText.Substring( PotQuoteEnd + 1, 1).Equals(QuoteDelim))
                        {
                            QuoteEnd = PotQuoteEnd;
                            fp_bQuoteFound = true;
                        }
                        else
                        {
                            PotQuoteEnd = PotQuoteEnd + 2;
                        }
                    }


                    if(fp_bQuoteFound == false)
                    {

                        throw new ApplicationException("<%IDS_DBREGION_ASPNET_ERROR_NO_MATCH_QUOTE%>");
                    }

                    if(colonStart > QuoteEnd)  //there is no user input in this literal string
                    {
                        curChar = QuoteEnd + 1;
                        continue;
                    }

                }

                Start = colonStart;
                // found a opening ::, find the close ::
                End = sqlText.IndexOf("::", Start + 2);


                if (End == -1)
                {
                    throw new ApplicationException( "<%IDS_DBREGION_ASPNET_ERROR_COLON_COLON_CUSTOM%>");
                }

                if (!fp_bQuoteFound)
                {
                    QuoteEnd = End + 1;
                }

                String Field = sqlText.Substring( Start + 2, End - (Start + 2));
                String Value = GetUserField(query, Field);


                if (fp_bQuoteFound)
                {

                    String Lead = sqlText.Substring( QuoteStart + 1, colonStart - QuoteStart -1);
                    String Tail = sqlText.Substring( End + 2, QuoteEnd - End - 2);
                    if (QuoteDelim ==  '\"')
                    {
                        Lead = Lead.Replace("\"\"", "\"");
                        Tail = Tail.Replace("\"\"", "\"");
                    }
                    else  if (QuoteDelim == '\'' )
                    {
                        Lead = Lead.Replace("''", "'");
                        Tail = Tail.Replace("''", "'");
                    }

                    Value = Lead + Value + Tail;
                }

                if(fp_bQuoteFound == false)
                {
                    if(Value.Length == 0)
                    {
                        Value = "0";
                    }
                    try{
                    parameterList.Add( Convert.ToDecimal(Value) );
                    }
                    catch( FormatException )
                    {
                        parameterList.Add( Convert.ToDecimal(0));
                    }
                }
                else
                {
                    parameterList.Add( Value );
                }

                if((sqlText.Length - QuoteEnd) == 1 )
                {
                    sqlText = sqlText.Substring(0, QuoteStart) + "?";
                }
                else
                {
                    sqlText = sqlText.Substring(0, QuoteStart) + "?" + sqlText.Substring( QuoteEnd + 1);
                }

                // Fixup the new current position to be after the substituted value
                curChar = QuoteStart + 1;
            }

            return sqlText;
        }
</SCRIPT>
<asp:Button ID="SubmitButton" Text="Submit" OnClick="ClickSubmitButton" RunAt="server" />
<asp:Button ID="ResetButton" Text="Reset" OnClick="ClickResetButton" RunAt="server" />
<asp:Label ID="debugOutputLabel" Runat="server" />